﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;

namespace Exercises
{
    class Exercise
    {
        public static void OrdersFromYearToCountry(int year, string country)
        {
            DataClassNorthwindDataContext context = new DataClassNorthwindDataContext();
            using (context)
            {
                var customers =
                    (from order in context.Orders
                    join customer in context.Customers on order.CustomerID equals customer.CustomerID
                    where order.OrderDate.Value.Year == year && order.ShipCountry == country
                    select customer).Distinct();
                Console.WriteLine(customers.Count());
            };
        }

        public static void OrdersFromYearToCountry()
        {
            DataClassNorthwindDataContext context = new DataClassNorthwindDataContext();
            using (context)
            {
                var customers =
                    context.ExecuteQuery<string>("SELECT DISTINCT c.ContactName " +
                        "FROM Customers c " +
                        "JOIN Orders o ON c.CustomerID = o.CustomerID " +
                        "WHERE o.ShipCountry = 'Canada' AND YEAR(o.OrderDate) = 1997 ");                
                Console.WriteLine(customers.Count());
            };
        }

        public static void OrdersInPeriod(DateTime start, DateTime end, string region)
        {
            DataClassNorthwindDataContext context = new DataClassNorthwindDataContext();
            using (context)
            {
                var res =
                    from order in context.Orders
                    where (order.ShippedDate > start) && (order.ShippedDate <end) && order.ShipRegion == region
                        //where (order.ShippedDate.Value.CompareTo(start) > 0) && (order.ShippedDate.Value.CompareTo(end) < 0) && order.ShipRegion == region
                    select order;
                Console.WriteLine(res.Count());
            }
        }

        public static void CreateCopyOfDataBase()
        {
            DataClassNorthwindDataContext cont = new DataClassNorthwindDataContext(
                        @"Server=.\SQLExpress;" +
                        @"AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\NorthWindTwin.mdf;" +
                        @"Database=NorthWindTwin; Trusted_Connection=yes");
            cont.CreateDatabase();
        }

        public static void NewOrder(string customerID, int? employeeID, DateTime? orderDate, DateTime? requiredDate, DateTime? shippedDate, int? shipVia, decimal? freight, 
            string shipAddress, string shipCity, string shipRegion, string shipPostalCode, string shipCounty, string shipName, List<Order_Detail> details)
        {
            TransactionScope scope = new TransactionScope();
            using (scope)
            {
                DataClassNorthwindDataContext context = new DataClassNorthwindDataContext();
                using (context)
                {
                    Order newOrder = new Order()
                    {
                        CustomerID = customerID,
                        EmployeeID = employeeID,
                        OrderDate = orderDate,
                        RequiredDate = requiredDate,
                        ShippedDate = shippedDate,
                        ShipVia = shipVia,
                        Freight = freight,
                        ShipAddress = shipAddress,
                        ShipCity = shipCity,
                        ShipRegion = shipRegion,
                        ShipPostalCode = shipPostalCode,
                        ShipCountry = shipCounty,
                        ShipName = shipName
                    };
                    //context.Orders.InsertOnSubmit(newOrder);
                    int orderId =
                    (from order in context.Orders
                     where (order.CustomerID == customerID && order.EmployeeID == employeeID && order.OrderDate == orderDate && order.RequiredDate == requiredDate &&
                         /*order.ShippedDate == shippedDate )&& */order.ShipVia == shipVia && order.Freight == freight && order.ShipAddress == shipAddress && order.ShipCity == shipCity &&
                         /*order.ShipRegion == shipRegion && */order.ShipPostalCode == shipPostalCode && order.ShipCountry == shipCounty && order.ShipName == shipName)
                     select order.OrderID).First();
                    foreach (Order_Detail item in details)
                    {
                        item.OrderID = orderId;
                        context.Order_Details.InsertOnSubmit(item);
                    }
                    try
                    {
                        context.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("ERROR");
                    }
                    finally
                    {
                        scope.Complete();
                    }
                }
            }
        }

        static void Main()
        {
            DataClassNorthwindDataContext context = new DataClassNorthwindDataContext();
            using (context)
            {                
                //Customer cust = new Customer()
                //{
                //    CustomerID = "AAAAA",
                //    CompanyName = "Telerik",
                //    ContactName = "Kamen Ivanov",
                //    ContactTitle = "Sofwtware Developer",
                //    Address = "Verenishka 11",
                //    City = "Sofia",
                //    Region = "Sofia",
                //    PostalCode = "1000",
                //    Country = "Bulgaria",
                //    Phone = "0883",
                //    Fax = "no fax",
                //};
                //DaoCustomer.InsertCustomer(cust);
                //Customer newCust = new Customer()
                //{
                //    CustomerID = cust.CustomerID,
                //    CompanyName = cust.CompanyName,
                //    ContactName = cust.ContactName,
                //    ContactTitle = cust.ContactTitle,
                //    Address = cust.Address,
                //    City = "Sofia",
                //    Region = "Sofia",
                //    PostalCode = "1000",
                //    Country = "Bulgaria",
                //    Phone = "0883",
                //    Fax = "no fax",
                //};

                //DaoCustomer.ModifyCustomer(cust, newCust);
                //DaoCustomer.InsertCustomer(cust);
                //DaoCustomer.DeleteCustomer(cust);
                //OrdersFromYearToCountry(1997, "Canada"); -> 3
                //OrdersFromYearToCountry();  
                //OrdersInPeriod(new DateTime(1996, 1, 1), DateTime.Now, "DF");
                //CreateCopyOfDataBase();
                //var res =
                //    (from e in context.Employees
                //     where e.EmployeeID == 1
                //     select e).First();
                
                //foreach (var item in res.Territories)
                //{
                //    Console.WriteLine(item.TerritoryDescription);
                //}

                //List<Order_Detail> details = new List<Order_Detail>() 
                //{
                //    new Order_Detail()
                //    {
                //        ProductID = 1,
                //        UnitPrice = 3.4m,
                //        Quantity = 3,
                //        Discount = 0f
                //    },

                //    new Order_Detail()
                //    {
                //        ProductID = 2,
                //        UnitPrice = 4,
                //        Quantity = 12,
                //        Discount = 0f
                //    },

                //    new Order_Detail()
                //    {
                //        ProductID = 3,
                //        UnitPrice = 13.4m,
                //        Quantity = 30,
                //        Discount = 0f
                //    },
                //};
                //NewOrder("VINET", 1, new DateTime(1995, 5, 23), new DateTime(1995, 6, 23), null, 1, 23.3M, "Tosltoi 11", "Sofia", null, "1000", "Bulgaria", "ship", details);
                
                
                //select SUM(od.UnistPrice * od.Quantity - od.Discount * od .Quantity)
                //from Suppliers s
                //join Products p on s.SupplierID = p.SupplierID
                //join [Order Details] od on p.ProductID = od.ProductID
                //join Orders o on o.OrderID = od.OrderID
                //where s.CompanyName = 'Exotic Liquids' AND o.OrderDate 
                //BETWEEN CAST('1995-1-1' as DATETIME) AND CAST('2000-1-1' as DATETIME)

                Console.WriteLine(context.usp_Incomes(1, new DateTime(1995,01,01), new DateTime(2000,01,01)).First().Column1);

                //ALTER PROCEDURE usp_Incomes (@supplier varchar(14), @start DATETIME, @end DATETIME)
                //AS
                //    select SUM(od.UnitPrice * od.Quantity - od.Discount * od .Quantity)
                //    from Suppliers s
                //    join Products p on s.SupplierID = p.SupplierID
                //    join [Order Details] od on p.ProductID = od.ProductID
                //    join Orders o on o.OrderID = od.OrderID
                //    where s.CompanyName = @supplier AND o.OrderDate BETWEEN @start AND @end
                //GO

                //declare @start DATETIME = CAST('1995-1-1' as DATETIME)
                //declare @end DATETIME = CAST('2000-1-1' as DATETIME)

                //exec dbo.usp_Incomes 'Exotic Liquids', @start, @end
            }
        }
    }
}
